create database SIMS
/*drop database SIMS*/
go
use SIMS
go
create table users
(
	userId int primary key identity,
	position varchar(25),
	userName varchar(25) unique,
	pass varchar(25) 
)
/*drop table users*/
create table orders
(
	orderId int primary key identity,
	datetimes datetime,
	userId int foreign key references users(userid),
	type varchar(25),
	status varchar(25),
	total float
)
create table category
(
	catId varchar(5) primary key,
	catName varchar(25)
)
create table item
(
	itemId int primary key identity,
	itemName varchar(25),
	catId varchar(5) foreign key references category(catId),
	unit varchar(25),
	quantityAvailable int,
	quantityEndOfDay int,
	price float,
	
)
go
create table [order_item]
(
	orderId int foreign key references orders(orderId),
	itemId int foreign key references item(itemId),
	amount int,
	priceSell float ,
	primary key (orderId ,itemId),
)
go
create table report
(
	reportID int identity primary key,
	sender varchar (25) foreign key references users(userName),
	receiver varchar (25)foreign key references users(userName),
	content nvarchar(200),
	datetimes datetime
)

--tao du lieu mau

insert into  users values('sales manager','binhmv','123456')
insert into users values('sales person','thanhtv','123456')

select * from users

insert into category values('1','fruits')
insert into category values('2','vegetable')

select * from category

insert into item values ('apple','1','kg',10,10,10000)
insert into item values ('apple1','1','kg',20,20,20000)
insert into item values ('a1pple1','1','kg',20,20,20000)
insert into item values ('orange','1','kg',15,15,15000)


select * from item
select * from item where itemName like 'ap%' 

insert into orders values(GETDATE(),1,'sell','successed ',500)
insert into orders values(GETDATE(),1,'buy','cancelled',1000)
insert into orders values(GETDATE(),1,'buy','cancelled',2500)
insert into orders values(GETDATE(),1,'sell','successed ',10000)
insert into orders values(GETDATE(),1,'sell','pending',500)
insert into orders values(GETDATE(),1,'sell','cancelled',900)
insert into orders values(GETDATE(),1,'buy','successed ',1000)
insert into orders values(GETDATE(),1,'sell','pending',500)

select * from orders
insert into orders values('2013-4-20 14:15:20',1,'sell','pending',500)
insert into order_item values(1,1,10,10)
insert into order_item values(1,2,100,100)
insert into order_item values(1,4,20,200)
insert into order_item values(1,3,30,150)

insert into order_item values(2,1,10,10)
insert into order_item values(2,2,100,100)
insert into order_item values(2,4,20,200)
insert into order_item values(2,3,30,150)

insert into order_item values(3,1,10,10)
insert into order_item values(3,2,100,100)
insert into order_item values(3,4,20,200)
insert into order_item values(4,3,30,150)


select * from orders
select * from order_item
select * from item


go
create view [view_waitingBills]
as 
select orders.orderId,itemId,amount,priceSell from orders inner join order_item 
	on orders.orderId = order_item.orderId 
	where [status]='pending' and DATEADD(DD,1,datetimes)<GETDATE()
go

/* select tu 2 bang  lay tat ca*/
select orders.orderId ,itemId,orders.datetimes,orders.type, order_item.amount 
from orders inner join order_item on orders.orderId = order_item.orderId 



/* select tu 2 bang lay theo itemId */
select orders.[orderId] ,itemId,orders.datetimes,orders.type, order_item.amount 
from orders inner join order_item on orders.orderId = order_item.orderId where itemId=1 order by datetimes 
/*1 la id cua apple*/

/* tao view */
go
create view thongke AS
select orders.orderId ,itemId,orders.datetimes,orders.type, order_item.amount 
from orders inner join order_item on orders.orderId = order_item.orderId 
go
select * from [thongke]
select orderid from orders
/* tao view de lam hoa don tra ve*/

select * from orders inner join order_item on orders.orderId = order_item.orderId 
inner join item on order_item.itemId = item.itemId